/*
 *	This file is part of Lounge-Cast.
 *
 *  Author:  	Markus Moormann
 *	E-mail:  	mmoorman@uni-osnabrueck.de
 *	Copyright:	University of Osnabrueck (Zentrum Virtuos)
 * 	Website:	www.uni-osnabrueck.de
 *	Date:		16.02.2011
 *
 *	Lounge-Cast is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *	Lounge-Cast is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with Lounge-Cast.  If not, see <http://www.gnu.org/licenses/>.
 */
package backend.service;


import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import javax.imageio.ImageIO;

import org.apache.commons.io.FileUtils;
import org.apache.log4j.Logger;

import com.twmacinta.util.MD5;

import backend.dbConnection.ConnectionHelper;
import backend.helper.HelperClass;
import backend.vo.CategoryVO;
import backend.vo.FeedVO;
import backend.vo.GroupVO;
import backend.vo.ImageVO;
import backend.vo.MediaVO;
import backend.vo.SlideVO;
import backend.vo.TextVO;
import backend.vo.UserVO;
import backend.vo.VideoVO;
import backend.watcher.SlideWatcher;

/**
 * Class <code>MediaService</code> handles all function to save media information to 
 * a database or to get media information from it
 * 
 * @author Markus Moormann
 */
public class MediaService {
	
	/**
	 * a logger to log errors, debug information, etc.
	 */
	private static Logger LOG = Logger.getLogger(MediaService.class);
	/**
	 * the connection to database
	 */
	//private Connection connection;
	/**
	 * the statement so send to database
	 */
	//private Statement statement;
	/**
	 * preparedStatement for for escaped queries
	 */
	private PreparedStatement ps;
	/**
	 * <code>HashMap</code> containing the fields of needed tables
	 */
	private static HashMap<String, String[]> fields = new HashMap<String, String[]>();
	
	/**
	 * default constructor
	 * setting some field information if not already set
	 */
	public MediaService() {
		if(fields.size() == 0) {
			fields.put("image", new String[]{"`url`",
											 "`image_source`",
											 "`in_composition`",
											 "`width`",
											 "`height`",
											 "`aspect_ratio`"});
			fields.put("slide", new String[]{"`url`",
											 "`aspect_ratio`"});
			fields.put("text", new String[]{"`text`",
											"`formated_text`",
											"`background_color`",
											"`skin`",
											"`image`",
											"`video`",
											"`image_orientation`," +
											"`background_image`," +
											"`margin_bottom`",
											"`margin_left`",
											"`margin_top`",
											"`margin_right`"});
			fields.put("video", new String[]{"`subtitles`",
											 "`volume`",
											 "`in_point`",
											 "`url`",
											 "`out_point`",
											 "`aspect_ratio`",
											 "`in_composition`"});
		}
	}
	
	/**
	 * get a <code>BufferedImage</code> by given URL
	 * 
	 * @param url the URL of the file
	 * @return the a <code>BufferedImage</code> containing the image
	 */
	private BufferedImage getImage(String url) {
		
		BufferedImage im = null;
		Connection connection = null;
		Statement statement = null;
		try {
			
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectImageFromUploaded = 	"SELECT " +
													"path " +
												"FROM " +
													"uploaded_files " +
												"WHERE " +
													"url = '" + url + "'";
			ResultSet rs = statement.executeQuery(selectImageFromUploaded);
			
			if (rs.next()) {
				im = ImageIO.read(new File(rs.getString("path")));
			}
			if (im == null) {
				im = ImageIO.read(new URL(url));
			}
			
			
		} catch (SQLException e) {
			LOG.error(e);
		} catch (IOException e) {
			LOG.error(e);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return im;
	}
	
	/**
	 * save a video to database
	 * 
	 * @param video a <code>VideoVO</code> with detailed information
	 * @param user a <code>UserVO</code>; the user who added the media
	 * @return <code>true</code> on success, <code>false</code> else 
	 */
	public boolean saveVideo(VideoVO video, UserVO user) {
		Connection connection = null;
		Statement statement = null;
		try {
			LOG.debug("saving a video");
			connection = ConnectionHelper.getConnection();
			createMediaQuery(video, user);
			String videoQuery = "INSERT INTO "+
									"`video` " +
										"(" +
											"`media_id`,"  +
											"`url`," 	   +
											"`subtitles`," +
											"`volume`,"    +
											"`in_point`," 	   +
											"`out_point`," 	   +
											"`aspect_ratio`," +
											"`in_composition`" +
										") VALUES " +
										"(" +
											"MD5(?)," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?" +
										")";
			ps = connection.prepareStatement(videoQuery);
			ps.setString(1, video.media_id);
			ps.setString(2, video.url);
			ps.setString(3, video.subtitles);
			ps.setDouble(4, video.volume);
			ps.setDouble(5, video.in_point);
			ps.setDouble(6, video.out_point);
			ps.setString(7, video.aspect_ratio);
			ps.setString(8, Boolean.toString(video.in_composition));
			ps.execute();
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		
		return true;
	}
	


	/**
	 * update a Video
	 * 
	 * @param video the video to update 
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean updateVideo(VideoVO video) {
		Connection connection = null;
		Statement statement = null;
		try {
			updateMedia(video);
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			String videoQuery = "UPDATE "+
									"`video` " +
								"SET " +
									"`url` 		 = '" + video.url 		+ "'," +
									"`subtitles` = '" + video.subtitles + "'," +
									"`volume`	 = "  + video.volume 	+ " ," +
									"`in_point`  = "  + video.in_point	+ " ," +
									"`out_point` = "  + video.out_point	+ " ," +
									"`aspect_ratio` ='" + video.aspect_ratio + "' " + 
								"WHERE " +
									"media_id = '" + video.media_id + "'";
			statement.execute(videoQuery);
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * save a slide to database
	 * 
	 * @param slide a <code>SlideVO</code> with detailed information
	 * @param user a <code>UserVO</code>, the user who added the media
	 * @return <code>true</code> on successful insert, <code>false</code> else
	 */
	public boolean saveSlide(SlideVO slide, UserVO user) {
		Connection connection = null;
		Statement statement = null;
		try {
			
			connection = ConnectionHelper.getConnection();
			
			MD5 md5 = new MD5();
			md5.Update(slide.media_id);
			
			createMediaQuery(slide, user);
			String slideQuery = "INSERT INTO "+
									"`slide` " +
										"(" +
											"`media_id`," +
											"`url`," +
											"`aspect_ratio` " +
										") VALUES " +
										"(" +
											"MD5(?)," +
											"?," +
											"?" +
										")";
			
			if(!slide.url.contains(HelperClass.getProperty("webserviceURL"))) {
				slide.url = insertRemoteFile(slide.url, true);
			}
			
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			ps = connection.prepareStatement(slideQuery);
			ps.setString(1, slide.media_id);
			ps.setString(2, slide.converted ? slide.url : HelperClass.getProperty("webserviceURL") + "/uploadedFiles/slides/" + md5.asHex());
			ps.setString(3, slide.aspect_ratio);
			ps.execute();
			
			String getUploadedFile = "SELECT " +
										"path " +
									 "FROM " +
									 	"uploaded_files " +
									 "WHERE " +
									 	"url = '" + slide.url + "'";
			ResultSet rs = statement.executeQuery(getUploadedFile);
			if(rs.next()) {
				new SlideWatcher(md5.asHex(), new File(rs.getString("path"))).start();
			}else if(!slide.converted) {//file is not on server, start SlideWatcher with URL so that SlideWatcher can download it
				new SlideWatcher(md5.asHex(), slide.url).start();
			}
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		
		return true;
	}
	
	/**
	 * 
	 * @param media
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean updateSlide(MediaVO media) {
		return true;
	}
	
	/**
	 * save a text to database
	 * 
	 * @param text a <code>TextVO</code> with detailed information
	 * @param user a <code>UserVO</code>, the user who added the media
	 * @return <code>true</code> on successful insert, <code>false</code> else
	 */
	public boolean saveText(TextVO text, UserVO user) {
		Connection connection = null;
		Statement statement = null;
		try {
			if(text.image != null && text.image.media_id != null && text.image.url != "")
			{
				LOG.debug("saving image for composition in textobject");
				saveImage(text.image, user);
			}
			
			if(text.video != null && text.video.media_id != null)
			{
				LOG.debug("saving video for composition in textobject");
				saveVideo(text.video, user);
			}
			
			LOG.debug("saving text...");
			connection = ConnectionHelper.getConnection();
			createMediaQuery(text, user);
			String textQuery = "INSERT INTO " +
									"`text` " +
										"(" +
											"`media_id`," 		+
											"`text`," 			+
											"`formated_text`,"  +
											"`background_color`,"  +
											"`skin`," 			+
											"`image`," 			+
											"`video`," 			+
											"`image_orientation`," +
											"`background_image`," +
											"`margin_bottom`," +
											"`margin_left`," +
											"`margin_top`," +
											"`margin_right`" +
										") VALUES " +
										"(" +
											"MD5(?)," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?" +
										")";
			
			MD5 media_id = new MD5();
			if(text.image != null && text.image.media_id != null && text.image.url != "") {
				media_id.Update(text.image.media_id);
			} else if(text.video != null && text.video.media_id != null) {
				media_id.Update(text.video.media_id);
			}
			
			ps = connection.prepareStatement(textQuery);
			ps.setString(1, text.media_id);
			ps.setString(2, text.text);
			ps.setString(3, text.formated_text);
			ps.setInt(4, text.background_color);
			ps.setString(5, text.skin);
			ps.setString(6, text.image != null && text.image.media_id != null && text.image.url != "" ? media_id.asHex() : null);
			ps.setString(7, text.video != null && text.video.media_id != null ? media_id.asHex() : null);
			ps.setString(8, text.image_orientation);
			ps.setString(9, text.background_image);
			ps.setInt(10, text.margin_bottom);
			ps.setInt(11, text.margin_left);
			ps.setInt(12, text.margin_top);
			ps.setInt(13, text.margin_right);
			ps.execute();
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		
		return true;
	}
	
	/**
	 * update text in database
	 * 
	 * @param text the text to update
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean updateText(TextVO text) {
		Connection connection = null;
		Statement statement = null;
		try {
			
			if(text.image != null)
			{
				updateImage(text.image);
			}
			if(text.video != null)
			{
				updateVideo(text.video);
			}
			
			LOG.debug("updating text...");
			updateMedia(text);
			
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String textQuery = "UPDATE " +
									"`text` " +
								"SET " +
									"`media_id`		 = ?," +
									"`text` 		 = ?," +
									"`formated_text` = ?," +
									"`background_color` = ?," +
									"`skin` 		 = ?," +
									"`image`		 = ?," +
									"`video`		 = ?," +
									"`image_orientation` = ?," +
									"`background_image` = ?, " +
									"`margin_bottom` = ?," +
									"`margin_left` = ?," +
									"`margin_top` = ?," +
									"`margin_right` = ? " +
								"WHERE " +
									"media_id = '" + text.media_id + "'";
			
			ps = connection.prepareStatement(textQuery);
			ps.setString(1, text.media_id);
			ps.setString(2, text.text);
			ps.setString(3, text.formated_text);
			ps.setInt(4, text.background_color);
			ps.setString(5, text.skin);
			ps.setString(6, text.image != null ? text.image.media_id : null);
			ps.setString(7, text.video != null ? text.video.media_id : null);
			ps.setString(8, text.image_orientation);
			ps.setString(9, text.background_image);
			ps.setInt(10, text.margin_bottom);
			ps.setInt(11, text.margin_left);
			ps.setInt(12, text.margin_top);
			ps.setInt(13, text.margin_right);
			
			ps.execute();
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		
		return true;
	}
	
	/**
	 * save an image to database
	 * 
	 * @param image an <code>ImageVO</code> with detailed information
	 * @param user a <code>UserVO</code>, the user who added the media
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean saveImage(ImageVO image, UserVO user) {
		Connection connection = null;
		Statement statement = null;
		try {
			
			if(!image.url.contains(HelperClass.getProperty("webserviceURL")) && !image.url.equals("")) {
				image.url = insertRemoteFile(image.url, false);
			}
			
			LOG.debug("saving image...");
			
			connection = ConnectionHelper.getConnection();
			
			createMediaQuery(image, user);
			String imageQuery = "INSERT INTO " +
									"`image` " +
										"(" +
											"`media_id`," 		+
											"`url`," 		+
											"`image_source`," +
											"`in_composition`," +
											"`width`," +
											"`height`," +
											"`aspect_ratio` "	+
										") VALUES " +
										"(" +
											"MD5(?)," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?" +
										")";
			
			int width = 0, height = 0;
			double aspect_ratio = 0;
			BufferedImage im = getImage(image.url);
			if(im != null) {
				width = im.getWidth();
				height = im.getHeight();
				aspect_ratio = (double)im.getWidth() / (double)im.getHeight();
			}
			
			connection = ConnectionHelper.getConnection();
			
			ps = connection.prepareStatement(imageQuery);
			ps.setString(1, image.media_id);
			ps.setString(2, image.url);
			ps.setString(3, image.image_source);
			ps.setString(4, Boolean.toString(image.in_composition));
			ps.setInt(5, width);
			ps.setInt(6, height);
			ps.setDouble(7, aspect_ratio);
			ps.execute();
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		
		return true;
	}
	
	/**
	 * update an image
	 * 
	 * @param image the image to update
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean updateImage(ImageVO image) {
		Connection connection = null;
		Statement statement = null;
		try {
			
			LOG.debug("updating image...");
			updateMedia(image);
			
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String imageQuery = "UPDATE " +
									"`image` " +
								"SET " +
									"`media_id`		 = ?," +
									"`url` 		 = ?," +
									"`image_source` = ?" +
								"WHERE " +
									"media_id = '" + image.media_id + "'";
			
			ps = connection.prepareStatement(imageQuery);
			ps.setString(1, image.media_id);
			ps.setString(2, image.url);
			ps.setString(3, image.image_source);
			
			ps.execute();
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		
		return true;
	}
	
	/**
	 * sets the approved status of a media
	 * 
	 * @param media_id the id of the media to update
	 * @param approved the status to set
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean setApprovedStatus(String media_id, boolean approved) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String updateApprovedStatus = 	"UPDATE " +
												"`media`" +
											"SET " +
												"`approved` = '" + approved + "' " +
											"WHERE " +
												"`media_id` = '" + media_id + "'";
			String updateFeedItem = "UPDATE " +
										"feed_item " +
									"SET " +
										"feed_item_status = '" + (approved ? "include" : "exclude") + "' " +
									"WHERE " +
										"feed_item_media_id = '" + media_id + "'";
			statement.execute(updateApprovedStatus);
			statement.execute(updateFeedItem);
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * get a prepared statement by image 
	 * 
	 * @param image the image containg information 
	 * @param user the user who wants to add the media
	 * @return a <code>PreparedStatement</code>
	 * @throws SQLException if an SQLException happens
	 */
	private PreparedStatement createMediaQuery(ImageVO image, UserVO user) throws SQLException {
		String valid_date = HelperClass.format(new Date(image.valid_date * 1000));
		String start_date = HelperClass.format(new Date(image.start_date * 1000));
		return createMediaQuery(image.media_id, 
								image.type, 
								image.title,
								start_date,
								valid_date,
								image.newsticker, 
								image.duration, 
								image.reference, 
								image.approved, 
								image.converted, 
								user,
								image.feed_item_uri,
								image.category,
								image.hide_title,
								image.title_color);	
	}
	
	/**
	 * get a prepared statement by video
	 * 
	 * @param video the video containing information
	 * @param user the user who wants to add the media
	 * @return a <code>PreparedStatement</code>
	 * @throws SQLException if an SQLException happens
	 */
	private PreparedStatement createMediaQuery(VideoVO video, UserVO user) throws SQLException {
		String valid_date = HelperClass.format(new Date(video.valid_date * 1000));
		String start_date = HelperClass.format(new Date(video.start_date * 1000));
		return createMediaQuery(video.media_id, 
								video.type, 
								video.title, 
								start_date,
								valid_date,
								video.newsticker, 
								video.duration, 
								video.reference, 
								video.approved, 
								video.converted, 
								user,
								video.feed_item_uri,
								video.category,
								video.hide_title,
								video.title_color);	
	}
	
	/**
	 * get a prepared statement by text
	 * 
	 * @param text the text containing information
	 * @param user the user who wants to add the media
	 * @return a <code>PreparedStatement</code>
	 * @throws SQLException if an SQLException happens
	 */
	private PreparedStatement createMediaQuery(TextVO text, UserVO user) throws SQLException {
		String valid_date = HelperClass.format(new Date(text.valid_date * 1000));
		String start_date = HelperClass.format(new Date(text.start_date * 1000));
		return createMediaQuery(text.media_id, 
								text.type, 
								text.title, 
								start_date,
								valid_date,
								text.newsticker, 
								text.duration, 
								text.reference, 
								text.approved, 
								text.converted, 
								user,
								text.feed_item_uri,
								text.category,
								text.hide_title,
								text.title_color);	
	}
	
	/**
	 * get a prepared statement by a slide
	 * 
	 * @param slide the slide containing information
	 * @param user the user who wants to add the media
	 * @return a <code>PreparedStatement</code>
	 * @throws SQLException if an SQLException occurs	 
	 */
	private PreparedStatement createMediaQuery(SlideVO slide, UserVO user) throws SQLException {
		String valid_date = HelperClass.format(new Date(slide.valid_date * 1000));
		String start_date = HelperClass.format(new Date(slide.start_date * 1000));
		return createMediaQuery(slide.media_id, 
								slide.type, 
								slide.title, 
								start_date,
								valid_date,
								slide.newsticker, 
								slide.duration, 
								slide.reference, 
								slide.approved, 
								slide.converted, 
								user,
								slide.feed_item_uri,
								slide.category,
								slide.hide_title,
								slide.title_color);	
	}
	
	/**
	 * create common insert statement for media
	 * 
	 * @param media_id the id of the media
	 * @param type the type of the media
	 * @param title the title of the media
	 * @param valid_date the valid date of the media
	 * @param newsticker whether newsticker is allowed or not
	 * @param duration the duration of the media
	 * @param reference further reference for the media
	 * @param approved whether the media is approved or not
	 * @param converted whether the media is converted or not
	 * @param user the user who wants to add the media 
	 * @return a prepared statement for inserting a media
	 * @throws SQLException if a SQLException occurs
	 */
	private PreparedStatement createMediaQuery(String media_id,
											   String type,
											   String title,
											   String start_date,
											   String valid_date,
											   boolean newsticker,
											   double duration,
											   String reference,
											   boolean approved,
											   boolean converted,
											   UserVO user,
											   String feed_item_uri,
											   CategoryVO category,
											   boolean hide_title,
											   int title_color) throws SQLException {
		Connection connection = null;
		try
		{
			connection = ConnectionHelper.getConnection();
			String mediaQuery = "INSERT INTO " +
									"`media` " +
										"(" +
											"`media_id`," 	+
											"`type`," 		+
											"`title`," 		+
											"`start_date`," +
											"`valid_date`," +
											"`newsticker`," +
											"`duration`," 	+
											"`reference`," 	+
											"`added_by`," 	+
											"`approved`," 	+
											"`converted`," +
											"`feed_item_uri`," +
											"`hide_title`," +
											"`category`," +
											"`title_color`" +
										") VALUES " +
										"(" +
											"MD5(?)," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?," +
											"?" +
										")";
			ps = connection.prepareStatement(mediaQuery);
			ps.setString(1, media_id);
			ps.setString(2, type);
			ps.setString(3, title);
			ps.setString(4, start_date);
			ps.setString(5, valid_date);
			ps.setString(6, Boolean.toString(newsticker));
			ps.setDouble(7, duration);
			ps.setString(8, reference);
			ps.setInt(9, user.user_id);
			ps.setString(10, Boolean.toString(approved));
			ps.setString(11, Boolean.toString(converted));
			ps.setString(12, feed_item_uri);
			ps.setString(13, Boolean.toString(hide_title));
			if(category == null) {
				ps.setNull(14, 0);
			} else {
				ps.setInt(14, category.category_id);
			}
			ps.setInt(15, title_color);
			ps.execute();
		} catch (SQLException e) {
			LOG.error(e);
		} finally
		{
			ConnectionHelper.close(connection);
		}
		return ps;
	}
	
	/**
	 * get all media belonging to a user
	 * 
	 * @param user a <code>UserVO</code> 
	 * @return <code>List&lt;MediaVO&gt;</code>
	 */
	public List<MediaVO> getUserMedia(UserVO user) {
		List<MediaVO> result = new LinkedList<MediaVO>();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectMedia = "SELECT " +
									"`media_id`," +
									"`type`," +
									"`title`," +
									"UNIX_TIMESTAMP(`start_date`) AS `start_date`," +
									"UNIX_TIMESTAMP(`valid_date`) as `valid_date`," +
									"`newsticker`," +
									"`duration`," +
									"`reference`," +
									"UNIX_TIMESTAMP(`added`) AS `added`," +
									"`approved`," +
									"`hide_title` " +
								 "FROM " +
								 	"`media` " +
								 "WHERE " +
								 	"`valid_date` > NOW() AND " +
								 	"`added_by` = " + user.user_id + " " +
								 "ORDER BY " +
								 	"`type` ASC," +
								 	"`title` ASC";
			ResultSet rs = statement.executeQuery(selectMedia);
			while(rs.next()) {
				MediaVO tmpMedia = new MediaVO(rs.getString("media_id"),
						  rs.getString("type"),
						  rs.getString("title"),
						  rs.getLong("start_date"),
						  rs.getLong("valid_date"),
						  rs.getBoolean("newsticker"),
						  rs.getLong("duration"),
						  rs.getString("reference"));
				tmpMedia.addedBy = user;
				tmpMedia.approved = rs.getBoolean("approved");
				tmpMedia.hide_title = rs.getBoolean("hide_title");
				MediaVO media = getMediaContent(tmpMedia);
				if(media != null && (media.type.equals("image") || media.type.equals("video"))){
					if(media.type.equals("image") && ((ImageVO)media).in_composition) {
						continue;
					}
					if(media.type.equals("video") && ((VideoVO)media).in_composition) {
						continue;
					}
					
				}
				result.add(media);
			}
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return result;
	}
	
	public List<List<MediaVO>> getExpiredMedia(long expired)
	{
		List<MediaVO> mediaList = new ArrayList<MediaVO>();
		List<List<MediaVO>> groupedMedia = new ArrayList<List<MediaVO>>();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectMedia = "SELECT " +
									"`m`.`media_id`," +
									"`m`.`type`," +
									"`m`.`title`," +
									"UNIX_TIMESTAMP(`m`.`valid_date`) as `valid_date`," +
									"`m`.`newsticker`," +
									"`m`.`duration`," +
									"`m`.`reference`," +
									"`m`.`approved`," +
									"`m`.`converted`," +
									"UNIX_TIMESTAMP(`m`.`added`) AS `added`," +
									"`m`.`added_by`," +
									"`u`.`user_name`," +
									"`u`.`group_id` " +
								 "FROM " +
								 	"`media` `m`," +
								 "LEFT JOIN " +
								 	"`feed_item` `fi` ON (`m`.`media_id` = `fi`.`feed_item_media_id`) " +
								 "LEFT JOIN " +
								 	"`feed` `f` ON (`fi`.`feed_id` = `f`.`feed_id`)," +
								 	"`user` `u`," +
								 "WHERE " +
								 	"`m`.`valid_date` < NOW() AND " +
								 	"UNIX_TIMESTAMP(`m`.`valid_date`) > " + expired + " AND " +
								 	"`m`.`converted` = 'true' AND " +
								 	"`m`.`added_by` = `u`.`user_id`" +
								 "ORDER BY " +
								 	"`type` ASC," +
								 	"`f`.`feed_id` ASC" +
								 	"`title` ASC";
			
			selectMedia = "SELECT " +
									"`m`.`media_id`," +
									"`m`.`type`," +
									"`m`.`title`," +
									"UNIX_TIMESTAMP(`m`.`start_date`) AS `start_date`," +
									"UNIX_TIMESTAMP(`m`.`valid_date`) as `valid_date`," +
									"`m`.`newsticker`," +
									"`m`.`duration`," +
									"`m`.`reference`," +
									"`m`.`approved`," +
									"`m`.`converted`," +
									"UNIX_TIMESTAMP(`m`.`added`) AS `added`," +
									"`m`.`added_by`," +
									"`u`.`user_name`," +
									"`u`.`group_id`," +
									"`f`.`title` as `feed_title`," +
									"`f`.`feed_id` " +
								 "FROM " +
								 	"`media` `m`" +
								 "LEFT JOIN " +
								 	"feed_item `fi` ON (`m`.`media_id` = `fi`.`feed_item_media_id`) " +
								 "LEFT JOIN " +
								 	"feed f ON (fi.feed_id = f.feed_id)," +
								 	"`user` `u` " +
								 "WHERE " +
								 	"`m`.`valid_date` < NOW() AND " +
								 	"UNIX_TIMESTAMP(`m`.`valid_date`) > " + expired + " AND " +
								 	"`m`.`converted` = 'true' AND " +
								 	"`m`.`added_by` = `u`.`user_id`" +
								 "ORDER BY " +
								 	"`type` ASC," +
								 	"f.feed_id ASC, " +
								 	"`title` ASC";
			LOG.debug(selectMedia);
			ResultSet rs = statement.executeQuery(selectMedia);
			String lastType = "";
			while(rs.next()) {
				if(!lastType.equals(rs.getString(2))) {
					lastType = rs.getString(2);
					if(mediaList.size() > 0) {
						groupedMedia.add(mediaList);
						mediaList = new ArrayList<MediaVO>();
					}
				}
				MediaVO tmpMedia = new MediaVO(rs.getString("media_id"),
								  rs.getString("type"),
								  rs.getString("title"),
								  rs.getLong("start_date"),
								  rs.getLong("valid_date"),
								  rs.getBoolean("newsticker"),
								  rs.getLong("duration"),
								  rs.getString("reference"));
				UserVO user = new UserVO(rs.getInt("added_by"), rs.getString("user_name"), new GroupVO(rs.getInt("group_id")));
				tmpMedia.addedBy = user;
				tmpMedia.approved = rs.getBoolean("approved");
				tmpMedia.converted = rs.getBoolean("converted");
				tmpMedia.feed = new FeedVO();
				tmpMedia.feed.feed_id = rs.getInt("feed_id");
				tmpMedia.feed.title = rs.getString("feed_title");
				
				//LOG.debug(tmpMedia.media_id + " get content");
				MediaVO media = getMediaContent(tmpMedia);
				//LOG.debug(tmpMedia.media_id + " result: " + media);
				if(media != null && (media.type.equals("image") || media.type.equals("video"))){
					//LOG.debug(media.media_id + "...check if in composition");
					if(media.type.equals("image") && ((ImageVO)media).in_composition) {
					//	LOG.debug(media.media_id + " is in composition do not add it");
						continue;
					}
					if(media.type.equals("video") && ((VideoVO)media).in_composition) {
						continue;
					}
				} 
				if(media != null)
				{
					mediaList.add(media);
				}
			}
			if(mediaList.size() > 0) {
				groupedMedia.add(mediaList);
			}
		} catch (SQLException e)
		{
			LOG.error(e);
		} finally
		{
			ConnectionHelper.close(statement);
			ConnectionHelper.close(connection);
		}
		return groupedMedia;
	}
	
	/**
	 * gets all currently valid media
	 *
	 * @param approved 
	 * @return a list of media
	 */
	public List<List<MediaVO>> getMedia(boolean approved, UserVO added_by) {
		List<MediaVO> mediaList = new ArrayList<MediaVO>();
		List<List<MediaVO>> groupedMedia = new ArrayList<List<MediaVO>>();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectMedia = "SELECT " +
									"`m`.`media_id`," +
									"`m`.`type`," +
									"`m`.`title`," +
									"UNIX_TIMESTAMP(`m`.`start_date`) AS `start_date`," +
									"UNIX_TIMESTAMP(`m`.`valid_date`) as `valid_date`," +
									"`m`.`newsticker`," +
									"`m`.`duration`," +
									"`m`.`reference`," +
									"`m`.`approved`," +
									"`m`.`converted`," +
									"`m`.`feed_item_uri`," +
									"`m`.`hide_title`, " +
									"`m`.`title_color`, " +
									"UNIX_TIMESTAMP(`m`.`added`) AS `added`," +
									"`m`.`added_by`," +
									"`u`.`user_name`," +
									"`u`.`group_id`," +
									"`f`.`title` as `feed_title`," +
									"`f`.`feed_id`," +
									"`c`.`category_id`," +
									"`c`.`name`, " +
									"`c`.`parent` " +
								 "FROM " +
								 	"`media` `m`" +
								 "LEFT JOIN " +
								 	"categories `c` ON (m.category = c.category_id) " +
								 "LEFT JOIN " +
								 	"feed_item `fi` ON (`m`.`media_id` = `fi`.`feed_item_media_id`) " +
								 "LEFT JOIN " +
								 	"feed f ON (fi.feed_id = f.feed_id)," +
								 	"`user` `u` " +
								 "WHERE " +
								 	"`m`.`valid_date` > NOW() AND " +
								 	(added_by == null ? "" : "`m`.`added_by` != " + added_by.user_id + " AND ") +
								 	"`m`.`approved` = '" + approved + "' AND " +
								 	"`m`.`converted` = 'true' AND " +
								 	"`m`.`added_by` = `u`.`user_id` " +
								 "ORDER BY " +
								 	"`type` ASC," +
								 	"`c`.`category_id` ASC," +
								 	"`f`.`feed_id`," +
								 	"`m`.`title` ASC";
			ResultSet rs = statement.executeQuery(selectMedia);
			String lastType = "";
			while(rs.next()) {
				if(!lastType.equals(rs.getString(2))) {
					lastType = rs.getString(2);
					if(mediaList.size() > 0) {
						groupedMedia.add(mediaList);
						mediaList = new ArrayList<MediaVO>();
					}
				}
				MediaVO tmpMedia = new MediaVO(rs.getString("media_id"),
								  rs.getString("type"),
								  rs.getString("title"),
								  rs.getLong("start_date"),
								  rs.getLong("valid_date"),
								  rs.getBoolean("newsticker"),
								  rs.getLong("duration"),
								  rs.getString("reference"));
				UserVO user = new UserVO(rs.getInt("added_by"), rs.getString("user_name"), new GroupVO(rs.getInt("group_id")));
				tmpMedia.addedBy = user;
				tmpMedia.feed = new FeedVO();
				tmpMedia.feed.feed_id = rs.getInt("feed_id");
				tmpMedia.feed.title = rs.getString("feed_title");
				tmpMedia.approved = rs.getBoolean("approved");
				tmpMedia.converted = rs.getBoolean("converted");
				tmpMedia.feed_item_uri = rs.getString("feed_item_uri");
				tmpMedia.hide_title = rs.getBoolean("hide_title");
				tmpMedia.title_color = rs.getInt("title_color");
				if(rs.getInt("category_id") != 0) {
					tmpMedia.category = new CategoryVO(rs.getInt("category_id"),
													   rs.getString("name"),
													   rs.getInt("parent"));
				}
				//LOG.debug(tmpMedia.media_id + " get content");
				MediaVO media = getMediaContent(tmpMedia);
				//LOG.debug(tmpMedia.media_id + " result: " + media);
				if(media != null && (media.type.equals("image") || media.type.equals("video"))){
					//LOG.debug(media.media_id + "...check if in composition");
					if(media.type.equals("image") && ((ImageVO)media).in_composition) {
					//	LOG.debug(media.media_id + " is in composition do not add it");
						continue;
					}
					if(media.type.equals("video") && ((VideoVO)media).in_composition) {
						continue;
					}
				} 
				if(media != null)
				{
					mediaList.add(media);
				}
			}
			if(mediaList.size() > 0) {
				groupedMedia.add(mediaList);
			}
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return null;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return groupedMedia;
	}
	
	/**
	 * get the real media to a given media_id and type
	 *
	 * @param media the media to get the content for
	 * @param connection the connection to the database
	 * @return a <code>MediaVO</code>
	 * @throws SQLException if an SQLException occurs
	 */
	public MediaVO getMediaContent(MediaVO media) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String getContent = "SELECT " +
									HelperClass.implode(fields.get(media.type), ",") +
								" FROM " +
									media.type + " " +
								"WHERE " +
									"media_id = '" + media.media_id + "'";
			ResultSet rs = statement.executeQuery(getContent);
			
			if(media.type.equals("slide")) {
				if(rs.next()) {
					return new SlideVO(media, rs.getString("url"), rs.getString("aspect_ratio"));
				}
			} else if(media.type.equals("text")) {
				if(rs.next()) {
					MediaVO imageMedia = media.clone();
					MediaVO videoMedia = media.clone();
					imageMedia.type = "image";
					imageMedia.media_id = rs.getString("image");
					videoMedia.type = "video";
					videoMedia.media_id = rs.getString("video");
					TextVO text = new TextVO(media, 
									  rs.getString("text"), 
									  rs.getString("formated_text"), 
									  rs.getInt("background_color"),
									  rs.getString("skin"),
									  (imageMedia.media_id == null) ? null : (ImageVO)getMediaContent(imageMedia),
									  (videoMedia.media_id == null) ? null : (VideoVO)getMediaContent(videoMedia),
									  rs.getString("image_orientation"),
									  rs.getString("background_image"),
									  rs.getInt("margin_bottom"),
									  rs.getInt("margin_left"),
									  rs.getInt("margin_top"),
									  rs.getInt("margin_right"));
					String sql = "SELECT " +
									"feed_item_pubDate " +
								 "FROM " +
								 	"feed_item " +
								 "WHERE " +
								 	"feed_item_uri = '" + media.feed_item_uri +  "' ";
					rs = statement.executeQuery(sql);
					if(rs.next()) {
						text.publish_date = rs.getDate("feed_item_pubDate");
					}
					return text;
				}
			} else if(media.type.equals("video")) {
				if(rs.next()) {
					return new VideoVO(media,
									   rs.getString("url"),
								   rs.getString("subtitles"),
								   rs.getDouble("volume"),
								   rs.getDouble("in_point"),
								   rs.getDouble("out_point"),
								   rs.getString("aspect_ratio"),
								   rs.getBoolean("in_composition"));
			}
		} else if(media.type.equals("image")) {
			if(rs.next())
			{
				return new ImageVO(media,
								   rs.getString("url"),
								   rs.getString("image_source"),
								   rs.getBoolean("in_composition"),
								   rs.getInt("width"),
								   rs.getInt("height"),
								   rs.getDouble("aspect_ratio"));
			}
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return null;
	}

	/**
	 * updates the information of media
	 * 
	 * @param media the media to update
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean updateMedia(MediaVO media) {
		String valid_date = HelperClass.format(new Date(media.valid_date * 1000));
		String start_date = HelperClass.format(new Date(media.start_date * 1000));
		Connection connection = null;
		try {
			connection = ConnectionHelper.getConnection();
			String updateMedia = "UPDATE `media` SET " +
								 	"`title` 		= ?," +
								 	"`start_date`	= ?," +
								 	"`valid_date`	= ?," +
								 	"`newsticker` 	= ?," +
								 	"`duration` 	= ?," +
								 	"`reference` 	= ?, " +
								 	"`category` 	= ?, " +
								 	"`hide_title` 	= ?, " +
								 	"`title_color` 	= ? " +
								 	(media.addedBy.user_group.group_id != 4 ? ",`added_by` 	= ? " : " ") +
								 "WHERE " +
								 	"`media_id` = '" + media.media_id + "'";
			ps = connection.prepareStatement(updateMedia);
			ps.setString(1, media.title);
			ps.setString(2, start_date);
			ps.setString(3, valid_date);
			ps.setString(4, Boolean.toString(media.newsticker));
			ps.setDouble(5, media.duration);
			ps.setString(6, media.reference);
			if(media.category == null) {
				ps.setNull(7, 0);
			} else {
				ps.setInt(7, media.category.category_id);
			}
			ps.setString(8, Boolean.toString(media.hide_title));
			ps.setInt(9, media.title_color);
			
			if(media.addedBy.user_group.group_id != 4) {
				ps.setInt(10, media.addedBy.user_id);
			}
			ps.execute();
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		}finally {
			ConnectionHelper.close(connection);
		}
		return true;
	}
	
	/**
	 * deletes a media from database, because of relational foreign keys 
	 * only the media itself has to be deleted
	 * 
	 * @param media a <code>MediaVO</code> containing the media_id to delete
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean deleteMedia(MediaVO media) {
		Connection connection = null;
		Statement statement = null;
		try {
			/*
			 * TODO check if this works with given media
			 */
			//Object mediaContent = getMediaContent(media.media_id, media.type);
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String url = "";
			//get the url 
			if(media instanceof TextVO && ((TextVO)media).image != null) 
			{
				url = ((TextVO)media).image.url;
			} else if(media instanceof VideoVO) 
			{
				url = ((VideoVO)media).url;
			} else if(media instanceof SlideVO) 
			{
				File file = new File(HelperClass.getProperty("generalUploadFolder") + File.separator + 
						HelperClass.getProperty("slideSubFolder") + File.separator + media.media_id);
				LOG.debug("try to delete slide directory: " + file);
				FileUtils.deleteQuietly(file);
			}
			//get path pointing to file
			String getFile = "SELECT " +
							 	"`path`," +
							 	"`file_id` " +
							 "FROM " +
							 	"`uploaded_files` " +
							 "WHERE " +
							 	"`url` = '" + url + "'";
			ResultSet rs = statement.executeQuery(getFile);
			if(rs.next())
			{
				String deleteFile = "DELETE FROM " +
										"`uploaded_files` " +
									"WHERE " +
										"`file_id` = '" + rs.getString("file_id") + "'";
				LOG.debug("try to delete image or video: " + rs.getString("path"));
				FileUtils.deleteQuietly(new File(rs.getString("path")));
				statement.execute(deleteFile);
			}
			String deleteMedia = "DELETE FROM " +
								 	"`media` " +
								 "WHERE " +
								 	"`media_id` = '" + media.media_id + "' ";
			LOG.info("deleting media:"+media.media_id);
			statement.execute(deleteMedia);
//			if(media.type.equals("text") && ((TextVO)media).image != null) {
//				deleteMedia(((TextVO)media).image);
//			}
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * insert the remote URL into database
	 * 
	 * @param remoteURL the URL to save
	 * @param slide true if remoteURL points to converted slides folder, false els
	 * 
	 * @return the new URL 
	 */
	private String insertRemoteFile(String remoteURL, boolean slide) {
		String url = HelperClass.getProperty("webserviceURL") + "/uploadedFiles/remote" + (slide ? "Slide" : "") + "/";
		MD5 remote_file_id = new MD5();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			remote_file_id.Update(remoteURL + new Date().getTime());
			
			url += remote_file_id.asHex();
			
			String insertRemote = 	"INSERT INTO " +
										"remote_files " +
											"(" +
												"remote_file_id," +
												"remote_url" +
											") VALUES" +
											"(" +
												"MD5(?)," +
												"?" +
											")";
			ps = connection.prepareStatement(insertRemote);
			ps.setString(1, remoteURL + new Date().getTime());
			ps.setString(2, remoteURL);
			ps.execute();
		} catch (SQLException e) {
			LOG.error("SQLException when inserting remoteURL" + e);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return url;
	}
	
}
